﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using GenericTools;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using DataModels.Common;
using DataModels.Enumerations;

namespace ReportingManagement
{
    public class ExcelTemplateSheet
    {
        #region Constants
        private const int SERVICE_TYPE_INDEX = 0;
        private const int SERVICE_PROVISION_INDEX = 1;
        private const string DEFAULT_SERVICE_PROVISION = DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY;
        
        private const string BOOLEAN_TRUE = "TRUE";
        private const string BOOLEAN_FALSE = "FALSE";
        #endregion

        #region Class Variables
        private int m_SheetID;
        private int m_TemplateID;
        private string m_TemplateSheetName;
        private string m_OutputSheetName;
        private string m_OutputSheetNameTemplate;
        private bool m_HasRepetitiveRows;
        private bool m_HasRepetitiveColumns;
        private int m_FirstRepetitiveColumnCellIndex = -1;
        private int m_FirstRepetitiveRowCellIndex = -1;
        private int m_LastRepetitiveColumnCellIndex = -1;
        private int m_LastRepetitiveRowCellIndex = -1;

        private object m_Tag;

        private ExcelTemplateCellCollection m_Cells;

        private bool m_Valid = false;
        private const string INVALID_OBJECT = @"Invalid object";
        #endregion

        #region Constructors
        private ExcelTemplateSheet(int sheetId)
        {
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                default:
                    string connectionString = Properties.Settings.Default.CacheSettingConnection;
                    OleDbConnection connection = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    cmd.Connection = connection;
                    command = cmd;
                    //command = GenericDataAccess.CreateCacheDBCommand();
                    break;
            }
            command.CommandType = CommandType.Text;
            command.CommandText = @"SELECT SheetID, TemplateID, TemplateSheetName, OutputSheetName,
                OutputSheetNameTemplate, HasRepetitiveRows, HasRepetitiveColumns, 
                FirstRepetitiveColumnCellIndex, FirstRepetitiveRowCellIndex, LastRepetitiveColumnCellIndex, 
                LastRepetitiveRowCellIndex FROM ExcelTemplateSheets WHERE SheetID = @SheetID";
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@SheetID";
            param.DbType = DbType.Int32;
            param.Value = sheetId;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
            if (dataTable.Rows.Count == 1)
            {
                DataRow row = dataTable.Rows[0];
                m_SheetID = sheetId;
                m_TemplateID = Int32.Parse(row["TemplateID"].ToString());
                m_TemplateSheetName = row["TemplateSheetName"].ToString();
                m_OutputSheetName = row["OutputSheetName"].ToString();
                m_OutputSheetNameTemplate = row["OutputSheetNameTemplate"].ToString();
                m_HasRepetitiveRows = Boolean.Parse(row["HasRepetitiveRows"].ToString());
                m_HasRepetitiveColumns = Boolean.Parse(row["HasRepetitiveColumns"].ToString());

                if (m_HasRepetitiveColumns)
                {
                    m_FirstRepetitiveColumnCellIndex = Int32.Parse(row["FirstRepetitiveColumnCellIndex"].ToString());
                    m_FirstRepetitiveRowCellIndex = Int32.Parse(row["FirstRepetitiveRowCellIndex"].ToString());
                    m_LastRepetitiveRowCellIndex = Int32.Parse(row["LastRepetitiveRowCellIndex"].ToString());
                }
                else
                {
                    try
                    {
                        m_FirstRepetitiveColumnCellIndex = Int32.Parse(row["FirstRepetitiveColumnCellIndex"].ToString());
                    }
                    catch { }
                    try
                    {
                        m_FirstRepetitiveRowCellIndex = Int32.Parse(row["FirstRepetitiveRowCellIndex"].ToString());
                    }
                    catch { }
                    try
                    {
                        m_LastRepetitiveRowCellIndex = Int32.Parse(row["LastRepetitiveRowCellIndex"].ToString());
                    }
                    catch { }
                }

                if (m_HasRepetitiveRows)
                {
                    m_FirstRepetitiveColumnCellIndex = Int32.Parse(row["FirstRepetitiveColumnCellIndex"].ToString());
                    m_FirstRepetitiveRowCellIndex = Int32.Parse(row["FirstRepetitiveRowCellIndex"].ToString());
                    m_LastRepetitiveColumnCellIndex = Int32.Parse(row["LastRepetitiveColumnCellIndex"].ToString());
                }
                else
                {
                    try
                    {
                        m_FirstRepetitiveColumnCellIndex = Int32.Parse(row["FirstRepetitiveColumnCellIndex"].ToString());
                    }
                    catch { }
                    try
                    {
                        m_FirstRepetitiveRowCellIndex = Int32.Parse(row["FirstRepetitiveRowCellIndex"].ToString());
                    }
                    catch { }
                    try
                    {
                        m_LastRepetitiveRowCellIndex = Int32.Parse(row["LastRepetitiveRowCellIndex"].ToString());
                    }
                    catch { }
                }

                m_Cells = new ExcelTemplateCellCollection();
                command.CommandText = @"SELECT CellID FROM ExcelTemplateCells WHERE SheetID = @SheetID";
                dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                foreach (DataRow r in dataTable.Rows)
                {
                    int cellID = Int32.Parse(r["CellID"].ToString());
                    ExcelTemplateCell cell = ExcelTemplateCell.CreateExcelTemplateCellFromDB(cellID);
                    m_Cells.Add(cell);
                }
                m_Valid = true;
            }
            else m_Valid = false;
            if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
        }

        private ExcelTemplateSheet() { }
        #endregion

        #region Public Methods
        public static ExcelTemplateSheet CreateExcelTemplateSheetFromDB(int sheetID)
        {
            ExcelTemplateSheet sheet = new ExcelTemplateSheet(sheetID);
            if (sheet.Valid) return sheet;
            else return null;
        }

        public static ExcelTemplateSheet CreateExcelTemplateSheet(DataModels.Models.ExcelTemplateSheet sheet)
        {
            var s = new ExcelTemplateSheet();
            s.m_Valid = true;
            s.m_SheetID = sheet.SheetID;
            s.m_TemplateID = sheet.TemplateID;
            s.m_TemplateSheetName = sheet.TemplateSheetName;
            s.OutputSheetName = sheet.OutputSheetName;
            s.OutputSheetNameTemplate = sheet.OutputsheetNameTemplate;
            s.m_HasRepetitiveRows = sheet.HasRepetitiveRows;
            s.m_HasRepetitiveColumns = sheet.HasRepetitiveColumns;
            
            s.m_FirstRepetitiveRowCellIndex = sheet.FirstRepetitiveRowCellIndex.HasValue ? sheet.FirstRepetitiveRowCellIndex.Value : -1;
            s.m_FirstRepetitiveColumnCellIndex = sheet.FirstRepetitiveColumnCellIndex.HasValue ? sheet.FirstRepetitiveColumnCellIndex.Value : -1;
            s.m_LastRepetitiveRowCellIndex = sheet.LastRepetitiveRowCellIndex.HasValue ? sheet.LastRepetitiveRowCellIndex.Value : -1;
            s.m_LastRepetitiveColumnCellIndex = sheet.LastRepetitiveColumnCellIndex.HasValue ? sheet.LastRepetitiveColumnCellIndex.Value : -1;

            s.Cells = new ExcelTemplateCellCollection();
            foreach (var cell in sheet.ExcelTemplateCells)
            {
                var c = ExcelTemplateCell.CreateExcelTemplateCell(cell);
                s.Cells.Add(c);
            }

            return s;
        }

        public void WriteToExcelSheet(Excel.Worksheet excelSheet, 
            Dictionary<string, string> dataset, int columnOffset, int rowOffset, bool overwriteDataSource = true, 
            DataRow facilityInfo = null)
        {
            if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);

            Excel.Range excelCells = excelSheet.Cells;
            foreach (ExcelTemplateCell cell in m_Cells)
            {
                bool dontSkipWriting = true;
                bool skipFalseValue = false;
                if (cell.CellExtras.Count > 0)
                {
                    if (cell.CellExtras.Count == 1)
                    {
                        var c = cell.CellExtras[0];
                        if (c.Extras == DataModels.Models.OtherSystemsFacilityDetail.SKIP_FALSE_VALUE) skipFalseValue = true;
                        if (c.Extras == DataModels.Models.OtherSystemsFacilityDetail.PMTCT_SERVICE_TYPE)
                        {
                            if (facilityInfo == null) dontSkipWriting = false;
                            else
                            {
                                string serviceProvision = facilityInfo[c.Extras].ToString();
                                if (string.IsNullOrWhiteSpace(serviceProvision)) dontSkipWriting = false;
                                else dontSkipWriting = true;
                            }
                        }
                    }

                    if (cell.CellExtras.Count > 1)
                    {
                        dontSkipWriting = false;
                        var serviceType = cell.CellExtras[SERVICE_TYPE_INDEX];
                        var serviceProvision = cell.CellExtras[SERVICE_PROVISION_INDEX];

                        switch (serviceProvision.Extras)
                        {
                            case DataModels.Models.OtherSystemsFacilityDetail.DSD:
                                dontSkipWriting = CanWeWriteToTheSheet(facilityInfo, serviceType.Extras, serviceProvision.Extras);
                                break;

                            case DataModels.Models.OtherSystemsFacilityDetail.TA_ONLY:
                                dontSkipWriting = CanWeWriteToTheSheet(facilityInfo, serviceType.Extras, serviceProvision.Extras);
                                break;

                            case DataModels.Models.OtherSystemsFacilityDetail.NEITHER:
                                dontSkipWriting = CanWeWriteToTheSheet(facilityInfo, serviceType.Extras, serviceProvision.Extras);
                                break;
                        }
                    }
                }

                if (dontSkipWriting)
                {
                    if (overwriteDataSource) cell.DataSource = dataset;
                    string value = cell.Value;
                    if (value == BOOLEAN_FALSE && skipFalseValue) continue;

                    int rowIndex = cell.CellRowIndex + rowOffset;
                    int columnIndex = cell.CellColumnIndex + columnOffset;
                    Excel.Range excelCell = (Excel.Range)excelCells[rowIndex, columnIndex];
                    try
                    {
                        excelCell.Value = value;
                    }
                    catch (Exception ex)
                    {
                        var exception = new Exception("Address: " 
                            + excelCell.AddressLocal + Environment.NewLine
                            + ex.Message, ex);
                        throw exception;
                    }

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelCell);
                    excelCell = null;
                }
            }
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelCells);
            excelCells = null;
        }

        private bool CanWeWriteToTheSheet(DataRow facilityInfo, string targetServiceType, string targetServiceProvision)
        {
            if (facilityInfo == null) return false;
            string serviceProvision = facilityInfo[targetServiceType].ToString();
            
            // If nothing is provided, we'll assume DEFAULT value
            //if (string.IsNullOrWhiteSpace(serviceProvision)) serviceProvision = DEFAULT_SERVICE_PROVISION;
            return (serviceProvision == targetServiceProvision);
        }

        public void PrepareCellForEvaluation(Dictionary<string, string> dataset)
        {
            foreach (ExcelTemplateCell cell in m_Cells)
            {
                cell.DataSource = dataset;
            }
        }

        public void WriteToExcelSheet_Alpha(Excel.Worksheet excelSheet,
            Dictionary<string, string> dataset, int columnOffset, int rowOffset)
        {
            if (!m_Valid) throw new InvalidOperationException(INVALID_OBJECT);

            Excel.Range excelCells = excelSheet.Cells;
            foreach (ExcelTemplateCell cell in m_Cells)
            {
                cell.DataSource = dataset;
                string value = cell.Value;
                int rowIndex = cell.CellRowIndex + rowOffset;
                int columnIndex = cell.CellColumnIndex + columnOffset;
                Excel.Range excelCell = (Excel.Range)excelCells[rowIndex, columnIndex];
                excelCell.Value = value;

                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelCell);
                excelCell = null;
            }
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelCells);
            excelCells = null;
        }

        /// <summary>
        /// Increment the value of the cell based on the data found in the data.
        /// data is the Dictionary whose key is the cell column index and the value is the increment.
        /// </summary>
        /// <param name="data">Dictionary object with whose key and value are cell column index and increment value respectively</param>
        public void AddValueToCells(Dictionary<int, int> data)
        {
            foreach (int cellColumn in data.Keys)
            {
                int value = data[cellColumn];
                foreach (ExcelTemplateCell cell in m_Cells)
                {
                    if (cell.CellColumnIndex == cellColumn)
                    {
                        try
                        {
                            string sValue = cell.Value;
                            int iValue = Int32.Parse(sValue);
                            int newValue = iValue + value;
                            cell.Value = newValue.ToString();
                        }
                        catch { }
                        break;
                    }
                }
            }
        }

        public bool ValidateSheet(Excel.Worksheet excelSheet)
        {
            bool okToContinue = false;
            Excel.Range excelCells = excelSheet.Cells;
            Excel.Range excelCell = null;

            foreach (var cell in Cells)
            {
                excelCell = (Excel.Range)excelCells[cell.CellRowIndex, cell.CellColumnIndex];
                if (excelCell.Value != null)
                {
                    string headerText = excelCell.Value as string;
                    if (headerText != cell.ColumnHeader)
                    {
                        m_Tag = cell;
                        break;
                    }
                }
            }

            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelCell);
            excelCell = null;
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelCells);
            excelCells = null;

            return okToContinue;
        }
        #endregion

        #region Public Properties
        public bool Valid
        {
            get
            {
                return m_Valid;
            }
        }

        public int SheetID
        {
            get
            {
                if (m_Valid) return m_SheetID;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set 
            { 
                m_SheetID = value;
                m_Valid = true;
            }
        }

        public int TemplateID
        {
            get
            {
                if (m_Valid) return m_TemplateID;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set
            {
                m_TemplateID = value;
                m_Valid = true;
            }
        }

        public string TemplateSheetName
        {
            get
            {
                if (m_Valid) return m_TemplateSheetName;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_TemplateSheetName = value; }
        }

        public string OutputSheetName
        {
            get
            {
                if (m_Valid) return m_OutputSheetName;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_OutputSheetName = value; }
        }

        public string OutputSheetNameTemplate
        {
            get
            {
                if (m_Valid) return m_OutputSheetNameTemplate;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_OutputSheetNameTemplate = value; }
        }

        public bool HasRepetitiveRows
        {
            get
            {
                if (m_Valid) return m_HasRepetitiveRows;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_HasRepetitiveRows = value; }
        }

        public bool HasRepetitveColumns
        {
            get
            {
                if (m_Valid) return m_HasRepetitiveColumns;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_HasRepetitiveColumns = value; }
        }

        public int FirstRepetitiveColumnCellIndex
        {
            get
            {
                if (m_Valid)
                {
                    if (m_FirstRepetitiveColumnCellIndex != -1) return m_FirstRepetitiveColumnCellIndex;
                    else throw new InvalidOperationException(INVALID_OBJECT);
                }
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_FirstRepetitiveColumnCellIndex = value; }
        }

        public int FirstRepetitiveRowCellIndex
        {
            get
            {
                if (m_Valid)
                {
                    if (m_FirstRepetitiveRowCellIndex != -1) return m_FirstRepetitiveRowCellIndex;
                    else throw new InvalidOperationException(INVALID_OBJECT);
                }
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_FirstRepetitiveRowCellIndex = value; }
        }

        public int LastRepetitiveColumnCellIndex
        {
            get
            {
                if (m_Valid)
                {
                    if (m_LastRepetitiveColumnCellIndex != -1) return m_LastRepetitiveColumnCellIndex;
                    else throw new InvalidOperationException(INVALID_OBJECT);
                }
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_LastRepetitiveColumnCellIndex = value; }
        }

        public int LastRepetitiveRowCellIndex
        {
            get
            {
                if (m_Valid)
                {
                    if (m_LastRepetitiveRowCellIndex != -1) return m_LastRepetitiveRowCellIndex;
                    else throw new InvalidOperationException(INVALID_OBJECT);
                }
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_LastRepetitiveRowCellIndex = value; }
        }

        public ExcelTemplateCellCollection Cells
        {
            get
            {
                if (m_Valid) return m_Cells;
                else throw new InvalidOperationException(INVALID_OBJECT);
            }
            set { m_Cells = value; }
        }

        public object Tag
        {
            get
            {
                return m_Tag;
            }
            set
            {
                m_Tag = value;
            }
        }
        #endregion
    }

    public class ExcelTemplateSheetCollection : List<ExcelTemplateSheet>
    {

    }
}
